<?php
namespace Common\Model;
use Think\Model;

/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2017/7/11 0011
 * Time: 下午 15:37
 */
class CommonModel extends Model
{



    /**
     * 导出数据为excel表格
     * @param $data  array   一个二维数组,结构如同从数据库查出来的数组   rechargeType,v
     * @param $title array  excel的第一行标题,一个数组,如果为空则没有标题
     * @param $filename string 下载的文件名
     * @return string
     * @examlpe
    exportexcel($arr,array('id','账户','密码','昵称'),'文件名!');
     */
    public static function exportExcel($data=array(), $title=array(),$filename='report',$sheetName="数据"){
        if ('WINNT'==PHP_OS) {
            $filename = iconv("UTF-8", "gb2312", $filename);//windows
        }

        vendor("PHPExcel.PHPExcel");
        $objPHPExcel = new \PHPExcel();

        /*以下是一些设置 ，什么作者  标题啊之类的*/
        $objPHPExcel->getProperties()->setCreator("世纪网辰")
            ->setLastModifiedBy("世纪网辰")
            ->setTitle($filename)
            ->setSubject($filename);
        /*以下就是对处理Excel里的数据， 横着取数据，主要是这一步，其他基本都不要改*/

        $field=CommonModel::mkExcelField();
        $in=0;
        $rowNum=1;
        foreach($title as $kk=>$vv){
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($field[$in++].$rowNum, ''.$vv);
        }

        foreach($data as $k => $v){
            $rowNum++;
            $in=0;
            foreach($v as $kk=>$vv){
                $cellPos=$field[$in++].$rowNum;
                $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellPos,$vv);
            }
        }

        $objPHPExcel->getActiveSheet()->setTitle($sheetName);
        $objPHPExcel->setActiveSheetIndex(0);
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');

        $objWriter->save(WEB_ROOT.DIRECTORY_SEPARATOR.'data'.DIRECTORY_SEPARATOR.$filename.'.xlsx');
        $urlPath="/data/{$filename}.xlsx";
        return $urlPath;
    }


    /**
     * 文件另存为
     * @param array $data
     * @param array $title
     * @param string $filename
     */
    public static function exportExcel_saveas($data=array(), $title=array(), $filename='report'){

        header("Content-type:application/octet-stream");
        header("Accept-Ranges:bytes");
        header("Content-type:application/vnd.ms-excel");
        header("Content-Disposition:attachment;filename=".$filename.".xls");
        header("Pragma: no-cache");
        header("Expires: 0");
        //导出xls 开始
        if (!empty($title)){
            foreach ($title as $k => $v) {
                $title[$k]=iconv("UTF-8", "GB2312",$v);
            }
            $title= implode("\t", $title);
            echo "$title\n";
        }
        if (!empty($data)){
            foreach($data as $key=>$val){
                foreach ($val as $ck => $cv) {
                    $cv=str_replace("\t",'' ,$cv );
                    $cv=str_replace("\n",'' ,$cv );
                    $cv=str_replace("\r",'' ,$cv );
                    $data[$key][$ck]=mb_convert_encoding($cv,"GBK","UTF-8");
                }
                $data[$key]=implode("\t", $data[$key]);

            }
            echo implode("\n",$data);
        }
    }



    public static function mkExcelField(){
        $baseAlpha=str_split('ABCDEFGHIJKLMNOPQRSTUVWXYZ');
        $alphaArray=$baseAlpha;
        foreach ($baseAlpha as $a){
            $alphaArray[]='A'.$a;
        }
        foreach ($baseAlpha as $a){
            $alphaArray[]='B'.$a;
        }
        foreach ($baseAlpha as $a){
            $alphaArray[]='C'.$a;
        }
        foreach ($baseAlpha as $a){
            $alphaArray[]='D'.$a;
        }
        foreach ($baseAlpha as $a){
            $alphaArray[]='E'.$a;
        }
        return $alphaArray;
    }

}